ColdFusion + Apache POIを使って既存EXCELシートを更新する

やりたいことは何か？

Webフォームで入力されたを値を既存のEXCELひな形の指定したセルに対して値をセットして、それをcfdocumentタグを使ってPDF化したいというごく普通のことです

ColdFusion標準機能（cfspreadsheetタグ）を使ってできる操作は以下の通り

1. read: XLS 形式のファイルの内容を読み取ります。
2. update: 既存の XLS ファイルに新しいシートを追加します
3. write: 新しい XLS 形式のファイルを書き込むか、または既存のファイルを上書きします。

色々試行錯誤しましたが、ColdFusion標準機能のみではどうにもうまくいきませんでした。

とても便利そうなタグではありますが、既存のEXCELシートに対して特定セルの値のみを更新したりすることができないのはちょっと都合が悪いです。そこで標準のcfspreadsheetタグを使わずApache POIを使って既存EXCELシートの値更新を試します。

環境

1. OS : Windows7 64Bit
2. CF : ColdFusion10 Developer版
3. POI Version : 3.9

準備１

1. Apache POI公式から最新版をダウンロードします。（私が使っているのは少し古いpoi-bin-3.9-20121203.zipですが・・・）
2. ColdFusion Serviceを止めます
3. CFインストールディレクトリ/cfusion/lib配下のpoi*.jarを適当なバックアップディレクトリに退避させます。
4. 1.でダウンロード＆zip解凍したディレクトリにある6ファイルをCFインストールディレクトリ/cfusion/lib配下へコピーします。（下記はpoi-bin-3.9-20121203.zipの場合です。）
   1. poi-3.9-20121203.jar
   2. poi-examples-3.9-20121203.jar
   3. poi-excelant-3.9-20121203.jar
   4. poi-ooxml-3.9-20121203.jar
   5. poi-ooxml-schemas-3.9-20121203.jar
   6. poi-scratchpad-3.9-20121203.jar
5. ColdFusion Serviceを起動します

準備２

準備１まででも直接既存EXCELシートに対して更新することはできますが、

1. Workbook オブジェクトを作る
2. シートオブジェクトを作る
3. 各シートオブジェクトに行オブジェクトを作る
4. 各行オブジェクトから, セルオブジェクトを作る
5. セルオブジェクトに値を設定する

というロジックが必要になり、プログラムが長くなる傾向があります。そこでもう少し取扱いし易いようにラッパーを用意して、例えば行3のG列に指定した文字列を設定したい場合を挙げてみると引数には3,"G","設定したい文字列"を取るjava methodを用意してシンプルにロジックを組めるようにしました

<cfset xlsdoc = createObject("java","createdoc.core.doc.XlsDoc")>
<cfset xlsdoc.cellPlanText(3,"G","設定したい文字列")>

と書けばEXCELの行3列Gに指定した文字列を設定できるようになりプログラムがすっきりします。

その他に用意したjava methodは

1. cellNumber(int, java.lang.String, double)
2. addImage(java.lang.String, int, java.lang.String)

等々10個ほど作ってcreatedoc.jarとして作成し、CFインストールディレクトリ/cfusion/lib配下へコピー（jarコピー後はColdFusion Serviceの再起動が必要）

実際に動かしてみる

準備も終わったので実際にやりたいことができるかを確認します。

<cfcontent type="text/html; charset=utf-8">
<cfprocessingdirective pageEncoding="utf-8" suppressWhitespace="true">
<cfset filepath="C:\inetpub\wwwroot\poi_cf_sample"> <!--- パスの設定 --->
<cffile 
    action="copy" 
    source="#filepath#\ひな形.xls"
    destination="#filepath#\blog.xls">
<cfset xlsdoc = createObject("java","createdoc.core.doc.XlsDoc")>
<cfset xlsdoc.setTemplateFile("#filepath#\blog.xls")>
<cfset xlsdoc.open()> <!--- 更新対象のEXCELファイルのオープン --->
<cfset xlsdoc.setOutputFile("#filepath#\blog.xls")>
<cfset xlsdoc.sheet("sample")> <!--- 更新対象のEXCELシート名指定 --->

<cfset xlsdoc.addImage("#filepath#\test.png",3,"D")> <!--- 3行D列に画像を張り付ける--->

<cfset xlsdoc.cellPlanText(2,"B","みかん１箱")> <!--- 2行B列に文字列をセットする--->

<cfset xlsdoc.cellNumber(3,"B",3000)> <!--- 3行B列に数値3000をセットする--->

<cfset xlsdoc.save()> <!--- 更新対象のEXCELファイルの保存 --->

<!--- 上の行でEXCELが保存されているので、そのEXCELをPDF化する --->
<cfdocument format="PDF" filename="#filepath#\blog.pdf" srcfile="#filepath#\blog.xls"></cfdocument>

<!--- 変換が終わったらblog.pdfをブラウザで確認 --->
<cflocation url="/poi_cf_sample/blog.pdf">
</cfprocessingdirective>

使用したEXCELのひな形

POIで更新されたEXCEL

cfdocumentタグで変換して作成されたPDF

まとめ

行と列をEXCELシートを開きながらA1とかC8とか目視して引数に渡すだけなので人がわかりやすくロジックも組みやすくなりました

○○申込書や○○見積書などフォーマットが決まっているものにはPOIは非常に重宝できます。EXCEL側で定義されている計算式等はそのまま生きるので、合計金額等の計算もそのままOK。また画像も対応できるようにしたので、別途ハンコ等の画像データを用意して指定部分にハンコ画像を貼り付けることで担当者印や社印なども入った状態でPDFを生成でき実案件でも使えるかもしれません。